
from datetime import datetime, timedelta
from collections import namedtuple
import pyodbc
import pyrpclib as rpc
import httplib as http

pyodbc.lowercase = True
#FETCH_SPAN = timedelta(3)  # fetch three days of scheduling data
RESDATA_PREFIX = r'resdata\&'  # prefix for tables in RESDATA.TPS
RECORD_RESOURCE = 'DIGITAL RECORDING'  # resource flag to force recording of a session
RECORD_SUFFIX = ' (Recorder)'  # suffix added to room names for recorder clients
DLCLASS_PREFIX = 'DL '  # only resources with this prefix are considered DL classrooms

FETCH_UPDATE = """
SELECT transdate, transtime 
FROM booklog 
ORDER BY transdate DESC, transtime DESC
"""

FETCH_SESSN = """
SELECT act.actnumber, dat.datenumber, act.name, act.client, cli.email, 
       dat.date, dat.time, dat.enddate, dat.endtime, 
       act.prime, act.tentative, act.project, 
       act.repeattype, act.repnumber, act.week, act.weekday, 
       act.monday, act.tuesday, act.wednesday, act.thursday, 
       act.friday, act.saturday, act.sunday 
FROM  {0}activity act, {0}dates dat, {0}client cli 
WHERE act.actnumber = dat.actnumber AND act.client = cli.client 
      AND act.inactive = 0 AND dat.date >= ? AND dat.date < ?
ORDER BY dat.date, dat.time
""".format(RESDATA_PREFIX)

#FETCH_REPT = """
#SELECT repnumber, week, weekday, monday, tuesday, wednesday,
#       thursday, friday, saturday, sunday
#FROM  {0}activity WHERE actnumber = ?
#""".format(RESDATA_PREFIX)

FETCH_ROOMS = """
SELECT resource FROM {0}resact WHERE actnumber = ?
""".format(RESDATA_PREFIX)

FETCH_NOTE = """
SELECT notes FROM {0}actnotes WHERE actnumber = ?
""".format(RESDATA_PREFIX)

Ordinals = ('0th', '1st', '2nd', '3rd', '4th', 'last')
DaysOfWeek = ((None,        '?'),
              ('monday',    'M'),
              ('tuesday',   'T'),
              ('wednesday', 'W'),
              ('thursday',  'R'),
              ('friday',    'F'),
              ('saturday',  'S'),
              ('sunday',    'U'))

Session = namedtuple('Session', ('srcid',
                                 'title',
                                 'recur',
                                 'owner',
#                                 'email',
                                 'rooms',
                                 'start',
                                 'stop',
                                 'sects',
                                 'categ',
								 'status',
                                 'recarg'))

def stripcomment(name):
	head, sep, tail = name.partition('(')
	comm, sep, tail = tail.rpartition(')')
	return head.strip() + (tail if sep else comm)

contentmgr = http.ContentManager([http.TextMarshaller(), 
								  http.VarargsMarshaller(http.PickleMarshaller, safe_imports=(datetime,))])

def run(addr, dsn, interval, timeout):
	global scheduler, rpcserver
	scheduler = Scheduler(dsn, timeout)
	rpcserver = rpc.SyncRpcServer(addr, contentmgr)
	rpcserver.scheduler = scheduler
#	rpcserver.register = scheduler.register
#	rpcserver.unregister = scheduler.unregister
#	rpcserver.refresh = scheduler.refresh
#	rpcserver.fetch_schedule = scheduler.fetch_schedule
	rpcserver.running = True
	rpcserver.shutdown = shutdown
	rpcserver.interval = interval
	while rpcserver.running:
		scheduler.refresh()
		rpcserver.run(rpcserver.interval)

def shutdown():
	print "shutting down!"
	rpcserver.running = False
	rpcserver.close()
	scheduler.close()

class Scheduler(object):

	# timeout in seconds for client rpc connections,
	# fetch_span in days for amount of scheduling data to fetch
	def __init__(self, dsn, timeout=5, fetch_span=3):
		self.timeout = timeout
		self.fetch_span = fetch_span
		self.revision = None
		self.lastfetch = None
		self.sessions = {}
		self.clients = {}
		self.config = {'connections':{}}
		self.tpsconn = pyodbc.connect(dsn=dsn, autocommit=True)

	def close(self):
		for client in self.clients.values():
			client.close()
		self.tpsconn.close()

	def register(self, room):
		self.unregister(room)
		addr = rpcserver.client_addr
		client = rpc.SyncRpcClient(addr, contentmgr, self.timeout)
		self.clients[room] = client
		client.update_schedule(self.sessions.get(room))
		if room.endswith(RECORD_SUFFIX) and (room not in self.config['connections'] or 
											 self.config['connections'][room][0] != addr):
			self.push_config()
		self.config['connections'][room] = (addr, True, datetime.now())

	def unregister(self, room):
		if room in self.clients:
			self.clients[room].close()
			del self.clients[room]
		addr = rpcserver.client_addr
		self.config['connections'][room] = (addr, False, datetime.now())

	def fetch_config(self):
		return self.config

	def push_config(self):
		for room, client in self.clients.items():
			client.update_config(self.config)

	def fetch_schedule(self, room):
		return self.sessions.get(room)

	def refresh(self):
		curs1 = self.tpsconn.cursor()
		curs2 = self.tpsconn.cursor()
		# Check if the DB has changed today since the last fetch time
		print 'Executing:', FETCH_UPDATE
		row = curs1.execute(FETCH_UPDATE).fetchone()
		print 'Returned ', curs1.rowcount, ' rows'
		revision = datetime.combine(*row) if row else datetime.min
		today = datetime.today()
		endday = today + timedelta(self.fetch_span)
		if revision == self.revision and today.date() == self.lastfetch.date():
			return False
		self.revision = revision
		self.lastfetch = today
		self.sessions = {}
		print "executing SQL:", FETCH_SESSN
		for row in curs1.execute(FETCH_SESSN, today, endday):
			# This part tries to figure out the repetition type, i.e. 'MWF'.
			# It is imperfect because of how ResSched handles changes to
			# bookings. Any true value indicates that the session repeats.
			if row.repeattype == 2:   # Irregular
				recur = 'irregular'
			elif row.repeattype == 3: # Fixed days
				recur = '{}d'.format(row.repnumber)
			elif row.repeattype == 4: # Fixed weeks
				recur = '{}w'.format(row.repnumber)
			elif row.repeattype == 5: # Fixed months
				recur = '{}m'.format(row.repnumber)
			elif row.repeattype == 6: # Set days each week 
				recur = ''.join(char for day, char in DaysOfWeek[1:] if getattr(row, day))
			elif row.repeattype == 7: # Specific weekday of the month
				recur = '{}{}'.format(Ordinals[row.week], DaysOfWeek[row.weekday][1])
			else:
				recur = False
			# Fix up title, strip trailing '*' added by ResSched and remove
			# any comments in parentheses added by ETS.
			title = stripcomment(row.name)
			if title.endswith('*'):
				title = title.rstrip('*')
				recur = recur or 'unknown'
			# Status is 'vicc' only for recurring sessions which have a status other
			# than 'One time event' or 'Non DL'.
			if row.tentative == 4:
				status = 'canceled'
			elif row.tentative == 7:
				status = 'maintenance'
			elif row.tentative in (5, 8) or not recur:
				status = 'other'
			else:
				status = 'vicc'
#			categ = 'vicc' if recur and row.tentative not in (5, 8) else row.project
			# Fetch the DL rooms associated with this session. We want the room
			# marked as the primary resource to be the recording room. If the primary
			# resouce isn't a recording room, then the record flag is set to 'never'.
			if row.prime.startswith(DLCLASS_PREFIX):
#				room = stripcomment(row.prime[3:])
#				addr = self.cliaddr.get(room, None)
#				record = (room, addr, {})
				rooms = [stripcomment(row.prime[len(DLCLASS_PREFIX):]) + RECORD_SUFFIX]
#				record = 0
			else:
#				record = None
				rooms = [None]
#				record = -1
#			curs2.execute(FETCH_ROOMS, row.actnumber)
#			rooms = [stripcomment(subrow[0][3:]) for subrow in curs2 if subrow[0].startswith('DL ')]
			for subrow in curs2.execute(FETCH_ROOMS, row.actnumber):
#				if subrow.resource == RECORD_RESOURCE:
#					record = record or 1
				if subrow.resource.startswith(DLCLASS_PREFIX):
					rooms.append(stripcomment(subrow.resource[len(DLCLASS_PREFIX):]))
			# For vicc sessions, check for section data in Notes field to handle
			# sessions that are scheduled at different times on different days.
			sects = None
			recarg = {}
			subrow = curs2.execute(FETCH_NOTE, row.actnumber).fetchone()
			for line in subrow[0].splitlines() if subrow else ():
				tag, sep, val = line.partition(':')
				if not sep:
					continue
				elif tag == 'sections':
					sects = val.strip()
				elif tag == 'encoding':
					parms = [item.partition('=') for item in val.split(',')]
					recarg.update({key.strip(): val.strip() or True for key, sep, val in parms})
#					encarg = val.strip()
				elif tag == 'record':
					val = val.strip().lower()
					if val == 'always':
						recarg['record_flag'] = True
#						record = 1
					elif val == 'never':
						recarg['record_flag'] = False
#						record = -1
#					elif val == 'user':
#						record = 0
					# Any other data needed from Notes field, e.g. expiration date
					# or encoding parameters, could be retrieved here.
			# build session instance
			item = Session(srcid = (row.actnumber, row.datenumber),
						   title = title,
						   recur = recur,
						   owner = (row.client, row.email),
#						   email = row.email,
						   rooms = rooms,
						   start = datetime.combine(row.date, row.time),
						   stop  = datetime.combine(row.enddate, row.endtime),
						   sects = sects,
						   categ = row.project,  # this isn't currently used for anything
						   status = status,
						   recarg = recarg)
#			if record:
#				rooms.append(record[0] + ' (Recorder)')
			for room in rooms:
				self.sessions.setdefault(room, []).append(item)
#			sessnlst.append(item)
		curs1.close()
		curs2.close()
		# Push out new schedule info to all registered clients
		for room, client in self.clients.items():
			sched = self.sessions.get(room)
			client.update_schedule(sched)
		return True
#		return sessnlst
		

			

def testsql(dsn, sql):
	conn = pyodbc.connect(dsn=dsn, autocommit=True)
	curs = conn.cursor()
	print datetime.now()
	curs.execute(sql)
	print '+{:-^10}'.format('') * len(curs.description) + '+'
	print ''.join('|{:^10.10}'.format(col[0]) for col in curs.description) + '|'
	print '+{:-^10}'.format('') * len(curs.description) + '+'
	for row in curs:
		print ''.join('|{:10.10}'.format(str(item)) for item in row) + '|'
	print '+{:-^10}'.format('') * len(curs.description) + '+'
	print datetime.now()

if __name__ == '__main__':
	import sys
	dsn = sys.argv[1]
	sql = file(sys.argv[2]).read()
	print "Using DSN '{}' to execute:\n{}\n".format(dsn, sql)
	testsql(dsn, sql)
